import pool from './db'
const dayjs = require('dayjs')
import { session } from 'electron'

export const fetchTransactionList = async function(params) {
  return new Promise(async (resolve, reject) => {
    let connection;

    const { accountId, action, page, pageSize } = params;

    try {
      const pageNum = parseInt(page) || 1;      // 当前页码，默认1
      const pageSizeNum = parseInt(pageSize) || 20; // 每页条数，默认20
      const offset = (pageNum - 1) * pageSizeNum; // 计算偏移量

      connection = await pool.getConnection();

      // 构建查询参数和条件
      const queryParams = [accountId];
      let actionCondition = '';

      // 添加 action 过滤条件
      if (action) {
        actionCondition = ' AND action = ?';
        queryParams.push(action);
      }

      // 主查询获取数据
      const [rows] = await connection.query(
        `SELECT * FROM transactions
         WHERE accountId = ?
         ${actionCondition}
         ORDER BY createdAt DESC
         LIMIT ? OFFSET ?`,
        [...queryParams, pageSizeNum, offset]  // 使用转换后的数值
      );

      // 格式化日期
      const formattedRows = rows.map(transaction => ({
        ...transaction,
        transactionDate: dayjs(transaction.transactionDate).format('YYYY-MM-DD HH:mm:ss'),
        createdAt: dayjs(transaction.createdAt).format('YYYY-MM-DD HH:mm:ss'),
        updatedAt: dayjs(transaction.updatedAt).format('YYYY-MM-DD HH:mm:ss')
      }));

      // 计数查询 - FIXED: 直接传递参数数组
      const [totalRows] = await connection.query(
        `SELECT COUNT(*) AS total
         FROM transactions
         WHERE accountId = ?
         ${actionCondition}`,
        queryParams  // 直接传递参数数组，不加额外方括号
      );

      const total = totalRows[0].total || 0;  // 添加空值保护
      const totalPages = Math.ceil(total / pageSizeNum); // 使用转换后的数值
      const hasNext = pageNum < totalPages;  // 使用转换后的数值

      resolve({
        total: total,
        page: pageNum,          // 返回转换后的数值
        pageSize: pageSizeNum,   // 返回转换后的数值
        hasNext: hasNext,
        list: formattedRows
      });
    } catch (error) {
      console.error("Database Error:", error);
      reject(new Error("Failed to fetch transaction list"));
    } finally {
      if (connection) connection.release();
    }
  });
}

export const fetchAccountList = async function(params) {
  return new Promise(async (resolve, reject) => {
    let connection
    try {
      // 获取连接
      connection = await pool.getConnection()

      const { userId } = this

      // 执行查询
      const [rows] = params.assetId ? await connection.query(
        `SELECT
          accounts.*,
          assets.icon
        FROM accounts
        LEFT JOIN assets
          ON accounts.categoryId = assets.id
        WHERE accounts.userId = ? AND accounts.assetId = ?`,
        [userId, params.assetId]
      ) : await connection.query(
        `SELECT
          accounts.*,
          assets.icon
        FROM accounts
        LEFT JOIN assets
          ON accounts.categoryId = assets.id
        WHERE accounts.userId = ?`,
        [userId]
      )

      resolve(rows)
    } catch (error) {
      console.log(error)
      reject(error)
    } finally {
      if (connection) connection.release()
    }
  });
}

export const fetchAccountInfo = async function(params) {
  return new Promise(async (resolve, reject) => {
    let connection
    try {
      // 获取连接
      connection = await pool.getConnection()

      const { userId } = this

      // 执行查询
      const [rows] = await connection.query(`
        -- 查询指定账户的详细信息，同时获取其对应资产的图标和名称
        SELECT
          accounts.*,                 -- 获取 accounts 表中的所有字段
          assets.icon,                -- 获取关联资产的图标字段
          assets.name AS assetName    -- 获取关联资产的名称，并重命名为 assetName
        FROM accounts
        LEFT JOIN assets
          ON accounts.categoryId = assets.id  -- 用 accounts 表中的 categoryId 和 assets 的 id 进行关联
        WHERE accounts.userId = ? AND accounts.id = ?                 -- 查询指定账户 ID 的记录
      `, [userId, params.accountId]
      );

      if (rows.length === 0) {
        resolve(null)
      } else {
        resolve(rows[0])
      }
    } catch (error) {
      console.log(error)
      reject(error)
    } finally {
      if (connection) connection.release()
    }
  });
}

/**
 * * 获取首页一级目录
 *
 * @return {array} 数据
 * @author Colin
 */
export const fetchAssets = async function(params) {
  return new Promise(async (resolve, reject) => {
    let connection
    try {
      // 获取连接
      connection = await pool.getConnection()

      const { userId } = this

      const query = `
        -- 查询所有顶级资产及其关联账户的总金额
        SELECT
          a.*,                                       -- 选取 assets 表中所有字段
          COALESCE(SUM(ac.amount), 0) AS totalAmount -- 汇总关联账户的金额总和，若没有账户则为 0
        FROM
          assets a                                   -- 资产表（父表）
        LEFT JOIN
          accounts ac ON a.id = ac.assetId           -- 将账户表关联到资产表，通过 assetId 连接
          AND ac.userId = ?
        WHERE
          a.parentId = 0                             -- 仅查询顶级资产（即没有父资产的记录）
        GROUP BY
          a.id;                                      -- 按资产 ID 分组，保证每个资产一行
      `;
      // 执行查询
      const [rows] = await connection.query(query, [userId])

      resolve(rows)
    } catch (error) {
      console.log('error', error)
      reject(error)
    } finally {
      if (connection) connection.release()
    }
  });
}
/**
 * * 获取账户二级目录列表
 *
 * @param {number} id 父级目录ID
 * @return {array} 数据
 * @author Colin
 */
export const fetchSubclass = async function(params) {
  return new Promise(async (resolve, reject) => {
    let connection
    try {
      // 获取连接
      connection = await pool.getConnection()

      // 执行查询
      const [rows] = await connection.query('SELECT * FROM assets WHERE parentId = ?', [params.id])
      resolve(rows)
    } catch (error) {
      console.log(error)
      reject(error)
    } finally {
      if (connection) connection.release()
    }
  });
}

export const fetchAssetStatistics = async function(params) {
  return new Promise(async (resolve, reject) => {
    let connection
    try {
      // 获取连接
      connection = await pool.getConnection()

      const { userId } = this

      const query = `
        WITH last_transactions AS (
          SELECT
            t.*,
            ROW_NUMBER() OVER (
              PARTITION BY t.accountId
              ORDER BY t.transactionDate DESC, t.id DESC
            ) AS rn
          FROM transactions t
          WHERE t.isDeleted = 0 AND t.userId = ?
        )

        SELECT
          a.assetId,
          SUM(a.amount) AS accountBalance,

          COALESCE(SUM(CASE WHEN lt.action = 'INCOME' THEN lt.amount ELSE 0 END), 0) AS totalIncome,
          COALESCE(SUM(CASE WHEN lt.action = 'EXPENSE' THEN lt.amount ELSE 0 END), 0) AS totalExpense,
          COALESCE(
            SUM(CASE WHEN lt.action = 'INCOME' THEN lt.amount ELSE 0 END) -
            SUM(CASE WHEN lt.action = 'EXPENSE' THEN lt.amount ELSE 0 END), 0
          ) AS netAmount,

          GREATEST(MAX(a.updatedAt), MAX(lt.updatedAt)) AS lastUpdatedTime

        FROM accounts a
        LEFT JOIN last_transactions lt
          ON a.id = lt.accountId AND lt.rn = 1

        WHERE
          a.assetId = ? AND
          a.isDeleted = 0 AND
          a.userId = ?

        GROUP BY a.assetId;
      `;
      // 执行查询
      const [rows] = await connection.query(query, [userId, params.assetId, userId])

      if (rows[0]) {
        rows[0].lastUpdatedTime = dayjs(rows[0].lastUpdatedTime).format('YYYY-MM-DD')
      }

      resolve(rows[0])
    } catch (error) {
      console.log(error)
      reject(error)
    } finally {
      if (connection) connection.release()
    }
  });
}

export const fetchAssetChartData = async function(params) {
  return new Promise(async (resolve, reject) => {
    let connection;
    try {
      // 获取数据库连接
      connection = await pool.getConnection();

      const { userId } = this

      // 提取参数：年份默认当前年，assetId 可为单个或数组
      const { year = new Date().getFullYear(), assetId } = params;

      // 获取当前年份和当前月份
      const currentYear = new Date().getFullYear();
      const currentMonth = new Date().getMonth() + 1;

      // 确保 assetId 是数组格式，构造 SQL 占位符
      const assetIds = Array.isArray(assetId) ? assetId : [assetId];
      const placeholders = assetIds.map(() => '?').join(',');

      // 第一步：获取指定年份每月的最后交易记录
      const [monthlyTx] = await connection.query(`
        SELECT
          t1.accountId,
          t1.assetId,
          MONTH(t1.transactionDate) AS month,
          t1.balanceAfter
        FROM transactions t1
        INNER JOIN (
          SELECT
            accountId,
            assetId,
            MAX(transactionDate) AS lastDate
          FROM transactions
          WHERE
            assetId IN (${placeholders}) AND
            YEAR(transactionDate) = ? AND
            isDeleted = 0 AND
            userId = ?
          GROUP BY
            accountId, assetId, MONTH(transactionDate)
        ) t2 ON
          t1.accountId = t2.accountId AND
          t1.assetId = t2.assetId AND
          t1.transactionDate = t2.lastDate AND
          t1.isDeleted = 0 AND
          t1.userId = ?
      `, [...assetIds, year, userId, userId]);

      // 第二步：获取所有账户在查询年份之前的最后余额（跨年基准）
      const [baseBalances] = await connection.query(`
        SELECT
          t1.accountId,
          t1.assetId,
          t1.balanceAfter
        FROM transactions t1
        INNER JOIN (
          SELECT
            accountId,
            assetId,
            MAX(transactionDate) AS lastDate
          FROM transactions
          WHERE
            assetId IN (${placeholders}) AND
            YEAR(transactionDate) < ? AND
            isDeleted = 0 AND
            userId = ?
          GROUP BY
            accountId, assetId
        ) t2 ON
          t1.accountId = t2.accountId AND
          t1.assetId = t2.assetId AND
          t1.transactionDate = t2.lastDate AND
          t1.isDeleted = 0 AND
          t1.userId = ?
      `, [...assetIds, year, userId, userId]);

      // 构建年初基准余额数据结构 baseData[assetId][accountId] = balance
      const baseData = {};
      baseBalances.forEach(tx => {
        if (!baseData[tx.assetId]) baseData[tx.assetId] = {};
        baseData[tx.assetId][tx.accountId] = parseFloat(tx.balanceAfter);
      });

      // 构建每月的余额数据 monthlyData[month][assetId][accountId] = balance
      const monthlyData = {};
      monthlyTx.forEach(tx => {
        const month = tx.month;
        if (!monthlyData[month]) monthlyData[month] = {};
        if (!monthlyData[month][tx.assetId]) monthlyData[month][tx.assetId] = {};
        monthlyData[month][tx.assetId][tx.accountId] = parseFloat(tx.balanceAfter);
      });

      // 初始化每月总额和累计数据
      const monthlySums = {};
      let accumulatedData = JSON.parse(JSON.stringify(baseData));

      // 循环处理每个月份的累计余额
      for (let month = 1; month <= 12; month++) {
        const isFutureMonth = (year === currentYear) && (month > currentMonth);
        const isCurrentMonth = (year === currentYear) && (month === currentMonth);

        // 未来月份直接置 0
        if (isFutureMonth) {
          monthlySums[month] = 0;
          continue;
        }

        // 当前月但没有数据也置 0
        if (isCurrentMonth && !monthlyData[month]) {
          monthlySums[month] = 0;
          continue;
        }

        // 将当月的数据合并到累计数据中
        if (monthlyData[month]) {
          for (const assetId in monthlyData[month]) {
            if (!accumulatedData[assetId]) accumulatedData[assetId] = {};
            Object.assign(accumulatedData[assetId], monthlyData[month][assetId]);
          }
        }

        // 当前月特殊处理：用临时数据替代当月账户余额用于计算
        if (month === currentMonth && monthlyData[currentMonth]) {
          const tempData = JSON.parse(JSON.stringify(accumulatedData));
          for (const assetId in monthlyData[currentMonth]) {
            for (const accountId in monthlyData[currentMonth][assetId]) {
              if (tempData[assetId]) {
                tempData[assetId][accountId] = monthlyData[currentMonth][assetId][accountId];
              }
            }
          }

          // 计算当前月的余额总和
          monthlySums[currentMonth] = Object.values(tempData).flatMap(asset =>
            Object.values(asset)
          ).reduce((sum, balance) => sum + balance, 0);
        } else {
          // 常规月份：用累计数据计算总和
          monthlySums[month] = Object.values(accumulatedData).flatMap(asset =>
            Object.values(asset)
          ).reduce((sum, balance) => sum + balance, 0);
        }
      }

      // 整理成返回结果格式
      const result = [];
      for (let month = 1; month <= 12; month++) {
        const isFutureMonth = (year === currentYear) && (month > currentMonth);
        result.push({
          month,
          balanceSum: isFutureMonth ? "0.00" : (monthlySums[month] || 0).toFixed(2)
        });
      }

      // 成功返回结果
      resolve(result);
    } catch (error) {
      // 出现错误时返回错误信息
      console.error('[fetchAssetChartData] Error:', error);
      reject(error);
    } finally {
      // 无论成功失败都释放数据库连接
      if (connection) await connection.release();
    }
  });
}

export const fetchAssetAllChartData = async function(params = {}) {
  const { year = new Date().getFullYear() } = params;

  let connection;
  try {
    // 获取数据库连接
    connection = await pool.getConnection();

    // 并发请求资产和负债数据
    const [liabilitiesData, assetData] = await Promise.all([
      fetchAssetChartData.call(this, { year, assetId: [2] }),              // 负债类（如信用卡）
      fetchAssetChartData.call(this, { year, assetId: [1, 3, 4, 5] })       // 资产类（如现金、基金等）
    ]);

    // 组合资产负债净值数据
    const result = liabilitiesData.map((liability, index) => {
      const asset = assetData[index];

      const liabilityValue = parseFloat(liability.balanceSum);
      const assetValue = parseFloat(asset.balanceSum);
      const netValue = assetValue - liabilityValue;

      return {
        month: liability.month,
        totalAsset: assetValue.toFixed(2),
        totalLiability: liabilityValue.toFixed(2),
        netAsset: netValue.toFixed(2)
      };
    });

    return result;
  } catch (error) {
    console.error('[fetchAssetAllChartData] Error:', error);
    reject(error);
  } finally {
    if (connection) await connection.release();
  }
};